Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Error handling

Attempting to add a duplicate record is a common data-entry error. For example, the user might try to create a record, such as a customer record, using a unique key that already exists in the database. If a customer record already exists where the cust-num equals 1, and the user tries to add another customer with the same cust-num, the Progress 4GL generates an error. When this type of error occurs, the Progress 4GL tries to resolve the error by working its way back through the procedure, looking at each block header until it finds the closest block that has the error property. It then undoes and retries the block. See OpenEdge Development: Progress 4GL Handbook for more information about error handling.

Because the DataServer is accessing a non-OpenEdge database, it cannot detect duplicate key errors until the end of a transaction block. As a result, if the error occurs in a subtransaction, the Progress 4GL cannot detect the error until the end of the entire transaction block, so it performs default error handling for the transaction block.

This code example illustrates the Progress 4GL and DataServer error handling:

rep-blk:                                     /* DataServer returns control */ REPEAT:      /* here when a duplicate state */                                             /* key is encountered.         */ PROMPT-FOR customer.cust-num. /* DataServer prompts here on */ /* duplicate state key. */ FIND customer USING cust-num NO-ERROR. IF AVAILABLE customer THEN UPDATE customer.cust-num name. do-blk: /* OpenEdge DB returns control */ DO ON ERROR UNDO do-blk, RETRY do-blk: /* here when a duplicate state */ FIND state WHERE st.st = customer.st. /* key is encountered. */ DISPLAY state. SET state. /* OpenEdge DB prompts here on */ /* duplicate state key. */ END. END.

This procedure displays the screen shown below. The procedure prompts the user to enter data into the cust-num field, and then into the st-abbr field. Suppose a user enters an existing state (for example, NH) while the 4GL is processing the DO block. With an OpenEdge database if a duplicate key entry occurs in the DO block, control returns to the DO block. After the 4GL displays a message that the record exists, it reprompts the user for the state abbreviation. For example:

With the OpenEdge DataServer for ORACLE, if a duplicate key entry occurs in the DO block, the 4GL returns control to the REPEAT block. The procedure reprompts the user to enter the customer number after the inner transaction completes. For example:

When two users simultaneously attempt to create records with duplicate keys, another difference in behavior occurs. The Progress 4GL raises an error immediately, but ORACLE raises an error after the first transaction commits, and only if the second transaction does not roll back. It is important to note that the second attempt to create a duplicate key will wait until the first user sends the transaction. ORACLE does not notify the DataServer that it is waiting for the other user’s transaction to end, so the DataServer cannot produce a message on the client indicating the lock wait situation.

To avoid this difference, change the scope of the transaction so that it completes more quickly or make the key nonunique and enforce uniqueness at the application level. Another technique is to use a RELEASE or VALIDATE statement when you check for the key’s uniqueness.